/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.com.pps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import vn.com.pps.entities.CourseTeacher;
import vn.com.pps.entities.CourseTeacherDetail;

/**
 *
 * @author Dai
 */
public class CourseTeacherDAO extends BaseDAO implements ICourseTeacherDAO {

    @Override
    public int insert(CourseTeacher courseTeacher) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public int update(CourseTeacher courseTeacher) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public int delete(int id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public CourseTeacher getCourseTeacher(Integer id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public Page<CourseTeacherDetail> getCourseTeachers(int pageNo, int pageSize, String whereClause) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public int getCount(String whereClause) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public CourseTeacherDetail getTeacherByCourseID(Integer id) {
        try {
            String sqlQuery = "SELECT tblcourses.coursename,tblcourseteachers.CourseID,tblcourseteachers.TeacherID,tblteachers.Firstname,tblteachers.Lastname,tblteachers.Mobile from tblcourseteachers,tblteachers,tblcourses\n"
                    + "WHERE tblcourseteachers.TeacherID=tblteachers.ID AND tblcourseteachers.courseid=tblcourses.ID and tblcourseteachers.CourseID=" + id + " LIMIT 1";
            return (CourseTeacherDetail) getJdbcTemplate().queryForObject(sqlQuery, new RowMapper() {
                @Override
                public Object mapRow(ResultSet rs, int i) throws SQLException {
                    CourseTeacherDetail courseTeacherDetail = new CourseTeacherDetail();
                    courseTeacherDetail.setCourseID(rs.getInt("CourseID"));
                    courseTeacherDetail.setTeacherID(rs.getInt("TeacherID"));
                    courseTeacherDetail.setFirstName(rs.getString("Firstname"));
                    courseTeacherDetail.setLastName(rs.getString("Lastname"));
                    courseTeacherDetail.setMobile(rs.getString("Mobile"));
                    courseTeacherDetail.setCourseName(rs.getString("Coursename"));
                    return courseTeacherDetail;
                }
            });
        } catch (Exception ex) {
            return null;
        }

    }

    @Override
    public int getCountCourseByTeacher(Integer teacherID) {
        String sqlQueryCount = "SELECT COUNT(*) FROM TblCourseTeachers WHERE TeacherID=" + teacherID;
        return getJdbcTemplate().queryForInt(sqlQueryCount);
    }

    @Override
    public Page<CourseTeacherDetail> getCoursesByTeacher(int pageNo, int pageSize, Integer TeacherID) {
        try {
            String sqlQuery = "SELECT  TblCourseTeachers.CourseID,TblCourses.CourseName,TblCourses.Schedule,TblCourses.StartedDate,TblCourses.CourseLevelID FROM TblCourseTeachers,TblCourses WHERE TblCourseTeachers.CourseID=TblCourses.ID AND TblCourseTeachers.TeacherID =" + TeacherID;
            String sqlQueryCount = "SELECT COUNT(*) FROM TblCourseTeachers WHERE TeacherID=" + TeacherID;
            logger.info("@getCoursesByTeacher:" + sqlQuery);
            PaginationHelper<CourseTeacherDetail> ph = new PaginationHelper<CourseTeacherDetail>();
            return ph.fetchPage(
                    this.getJdbcTemplate(),
                    sqlQueryCount,
                    sqlQuery,
                    null,
                    pageNo,
                    pageSize, new ParameterizedRowMapper<CourseTeacherDetail>() {
                        @Override
                        public CourseTeacherDetail mapRow(ResultSet rs, int i) throws SQLException {
                            CourseTeacherDetail courseTeacherDetail = new CourseTeacherDetail();
                            courseTeacherDetail.setCourseName(rs.getString("CourseName"));
                            courseTeacherDetail.setCourseID(rs.getInt("CourseID"));
                            courseTeacherDetail.setSchedule(rs.getString("Schedule"));
                            courseTeacherDetail.setStartedDate(rs.getDate("StartedDate"));
                            courseTeacherDetail.setLevel(rs.getString("CourseLevelID"));
                            return courseTeacherDetail;
                        }
                    });
        } catch (Exception ex) {
            logger.error(ex.getMessage());
            return null;
        }

    }

}
